/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package quangtq;

import beans.Mail;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.ejb.Stateless;

/**
 *
 * @author QUANG2PRO
 */
@Stateless
public class MailPaging implements MailPagingRemote
{

    @Override
    public ArrayList<Mail> mailRange(int numOfPage, int numOfRecord, String userName, Boolean isInbox)
    {
        ArrayList<Mail> list = new ArrayList<Mail>();
        try
        {
            Connection con = helper.ConnectionHelper.GetConnection();

            String sql = "";
            if(isInbox)
            {
                sql = "WITH MailsRange AS (SELECT *, ROW_NUMBER() OVER (ORDER BY MailID) "
                    + "AS RowNumber FROM Mails WHERE UserName = ? AND Inbox = 1) "
                    + "SELECT MailID, FromUser, Subject FROM MailsRange "
                    + "WHERE RowNumber between ? and ? ";
            }
            else
            {
                sql = "WITH MailsRange AS (SELECT *, ROW_NUMBER() OVER (ORDER BY MailID) "
                    + "AS RowNumber FROM Mails WHERE UserName = ? AND Inbox = 0) "
                    + "SELECT MailID, ToUser, Subject FROM MailsRange "
                    + "WHERE RowNumber between ? and ? ";
            }

            PreparedStatement p = con.prepareStatement(sql);
            p.setString(1, userName);
            p.setInt(2, (numOfPage - 1) * numOfRecord + 1);
            p.setInt(3, numOfPage * numOfRecord);
            ResultSet rs = p.executeQuery();

            Mail mail = null;
            while (rs.next())
            {
                if(isInbox)
                {
                    mail = new Mail(rs.getInt("mailID"), rs.getString("fromUser"), rs.getString("subject"));
                }
                else
                {
                    mail = new Mail(rs.getInt("mailID"), rs.getString("toUser"), rs.getString("subject"));
                }
                list.add(mail);
            }
            System.out.print("select success!");
        }
        catch (SQLException ex)
        {
            System.out.println("SQL Exception: " + ex.toString());
        }

        return list;
    }
    
    @Override
    public int numberOfMails(String userName, Boolean isInbox)
    {
        int num = 0;
        try
        {
            Connection con = helper.ConnectionHelper.GetConnection();
            
            String sql = "";
            if(isInbox)
            {
                sql = "SELECT COUNT(*) FROM Mails WHERE UserName = ? AND Inbox = 1";
            }
            else
            {
                sql = "SELECT COUNT(*) FROM Mails WHERE UserName = ? AND Inbox = 0";
            }

            PreparedStatement p = con.prepareStatement(sql);
            p.setString(1, userName);
            ResultSet rs = p.executeQuery();

            if(rs.next())
            {
                num = rs.getInt(1);
            }
        }
        catch (SQLException ex)
        {
            System.out.println("SQL Exception: " + ex.toString());
        }

        return num;
    }

    @Override
    public int deleteMails(String[] ids)
    {
        int result = 0;
        try
        {
            Connection con = helper.ConnectionHelper.GetConnection();
            String sql = "DELETE FROM Mails WHERE MailID IN (";
            for(int i = 0; i < ids.length; i++)
            {
                if(i == ids.length - 1)
                {
                    sql += ids[i] + ")";
                }
                else
                {
                    sql += ids[i] + " ,";
                }
            }

            PreparedStatement p = con.prepareStatement(sql);
            result = p.executeUpdate();
        }
        catch (SQLException ex)
        {
            System.out.println("SQL Exception: " + ex.toString());
        }

        return result;
    }
}
